﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data.SQLite;
using MyFirstSolution.Common;
using MyFirstSolution.Model;
using System.Data;

namespace MyFirstSolution.DAL
{
    /// <summary>
    /// 数据库操作类
    /// </summary>
    public class MyDbHelper
    {
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        private string connstr;

        /// <summary>
        /// 数据库连接对象
        /// </summary>
        private SQLiteConnection condb;

        /// <summary>
        /// 创建数据库操作对象--自动创建数据库连接（连接字符串来自于配置文件）
        /// </summary>
        public MyDbHelper()
        {
            //从配置文件中获取连接字符串
            MyConfig config = new MyConfig();
            connstr = config.ConnectString;

            try
            {
                condb = new SQLiteConnection(connstr);
                condb.Open(); //连接SQLite数据库
            }
            catch
            {
                condb = null;
                return;
            }
        }

        /// <summary>
        /// 获取指定登录名称的用户信息
        /// </summary>
        /// <param name="name">用户登录名称</param>
        /// <returns>用户信息数据对象</returns>
        public User GetUserInfo(string name)
        {
            if (condb == null)
                return null;

            //生成SQL查询语句
            string cmdstr = "select * from [Users] where Account='" + name + "'";

            try
            {
                SQLiteCommand cmd = new SQLiteCommand(cmdstr, condb);
                SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds); //执行SQL查询语句

                if (ds.Tables[0].Rows.Count > 0)
                    return new User(ds.Tables[0].Rows[0]);
                else
                    return null;
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// 获取所有的用户数据
        /// </summary>
        /// <param name="list">存放查询结果的列表</param>
        /// <returns>是否读取成功？</returns>
        public bool GetAllUsers(ref List<User> list)
        {
            string cmdstr = "select * from [Users]";

            try
            {
                SQLiteCommand cmd = new SQLiteCommand(cmdstr, condb);
                SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    list.Add(new User(ds.Tables[0].Rows[i]));

                return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 修改用户表中的密码--Base64格式存储
        /// </summary>
        /// <param name="account">用户登录名</param>
        /// <param name="newpass">新密码值</param>
        /// <returns>是否修改成功？</returns>
        public bool UpdateUsersPassword(string account, string newpass)
        {
            string cmdstr = "update [Users] set Password='" + CodeProcess.EncodeBase64(newpass) +
                "' where Account='" + account + "'";

            try
            {
                SQLiteCommand cmd = new SQLiteCommand(cmdstr, condb);
                if (cmd.ExecuteNonQuery() > 0)
                    return true;
                else
                    return false;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 根据ID删除用户信息
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>

        public bool DeleteUserInfo(int ID)
        {
            string cmdstr = "delete from [Users] where ID = " + ID;

            try
            {
                SQLiteCommand cmd = new SQLiteCommand(cmdstr, condb);
                if (cmd.ExecuteNonQuery() > 0)
                    return true;
                else return false;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 插入一条用户信息
        /// </summary>
        /// <param name="user">用户实体类</param>
        /// <returns>是否插入成功</returns>
        public bool InsertUserInfo(User user)
        {
           // string cmdstr2 = "INSERT INTO "main"."Users"("Account", "UserType") VALUES ('ccc', 2)";
            string cmdstr = "insert into Users(Account,Password,UserType) values('" +user.Account + "','" + CodeProcess.EncodeBase64(user.Password) + "'," + user.UserType + ")";
            try
            {
                SQLiteCommand cmd = new SQLiteCommand(cmdstr, condb);
                if (cmd.ExecuteNonQuery() > 0)
                    return true;
                else return false;
            }
            catch
            {
                return false;
            }
        }
     
    }
}
